package com.tianhai.db;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

import com.tianhai.db.parser.ast.CreateTableStatement;
import com.tianhai.db.parser.ast.DeleteStatement;
import com.tianhai.db.parser.ast.InsertStatement;
import com.tianhai.db.parser.ast.SelectStatement;
import com.tianhai.db.parser.ast.UpdateStatement;

/**
 * ISQL - 交互式SQL命令行工具
 * 支持交互式SQL输入和执行
 * 支持从文件执行SQL脚本
 * 集成TransactionManager处理事务
 */
public class ISQL {
    
    private final Executor executor;
    private final TransactionManager txnManager;
    private Long currentTxnId = null;
    private boolean autoCommit = true;
    
    // 统计信息
    private int executedQueries = 0;
    private int successQueries = 0;
    private int failedQueries = 0;
    
    /**
     * 构造函数
     */
    public ISQL() throws IOException {
        this.executor = new Executor();
        this.txnManager = new TransactionManager("isql.wal");
    }
    
    /**
     * 交互式模式
     */
    public void interactiveMode() {
        System.out.println("========================================");
        System.out.println("  ISQL - 交互式SQL命令行工具 v1.0");
        System.out.println("========================================");
        System.out.println("输入SQL语句并按回车执行");
        System.out.println("输入 'exit' 或 'quit' 退出");
        System.out.println("输入 'help' 查看帮助");
        System.out.println("========================================\n");
        
        Scanner scanner = new Scanner(System.in);
        StringBuilder sqlBuffer = new StringBuilder();
        
        while (true) {
            // 显示提示符
            if (sqlBuffer.length() == 0) {
                if (currentTxnId != null) {
                    System.out.print("isql [txn:" + currentTxnId + "]> ");
                } else {
                    System.out.print("isql> ");
                }
            } else {
                System.out.print("   -> ");
            }
            
            String line = scanner.nextLine().trim();
            
            // 处理特殊命令
            if (line.equalsIgnoreCase("exit") || line.equalsIgnoreCase("quit")) {
                if (currentTxnId != null) {
                    System.out.println("警告: 有未提交的事务，自动回滚");
                    try {
                        txnManager.rollbackTransaction(currentTxnId);
                    } catch (Exception e) {
                        // 忽略
                    }
                }
                break;
            }
            
            if (line.equalsIgnoreCase("help")) {
                printHelp();
                continue;
            }
            
            if (line.equalsIgnoreCase("status")) {
                printStatus();
                continue;
            }
            
            // 处理COMMIT命令
            if (line.equalsIgnoreCase("commit") || line.equalsIgnoreCase("commit;")) {
                handleCommit();
                continue;
            }
            
            // 处理ROLLBACK命令
            if (line.equalsIgnoreCase("rollback") || line.equalsIgnoreCase("rollback;")) {
                handleRollback();
                continue;
            }
            
            if (line.isEmpty()) {
                continue;
            }
            
            // 累积SQL语句
            sqlBuffer.append(line).append(" ");
            
            // 检查是否是完整的SQL语句（以分号结尾）
            if (line.endsWith(";")) {
                String sql = sqlBuffer.toString().trim();
                sqlBuffer.setLength(0);
                
                // 移除末尾的分号
                if (sql.endsWith(";")) {
                    sql = sql.substring(0, sql.length() - 1).trim();
                }
                
                // 执行SQL
                executeSQL(sql);
            }
        }
        
        // 关闭资源
        cleanup();
        
        // 显示统计信息
        System.out.println("\n========================================");
        System.out.println("执行统计:");
        System.out.println("  总查询数: " + executedQueries);
        System.out.println("  成功: " + successQueries);
        System.out.println("  失败: " + failedQueries);
        System.out.println("========================================");
        System.out.println("再见！");
        
        scanner.close();
    }
    
    /**
     * 脚本模式
     */
    public void scriptMode(String filePath) {
        System.out.println("========================================");
        System.out.println("  执行SQL脚本: " + filePath);
        System.out.println("========================================\n");
        
        try {
            // 读取脚本文件
            String content = new String(Files.readAllBytes(Paths.get(filePath)));
            
            // 按分号分割SQL语句
            String[] statements = content.split(";");
            
            for (String sql : statements) {
                sql = sql.trim();
                if (!sql.isEmpty()) {
                    System.out.println("执行: " + sql);
                    executeSQL(sql);
                    System.out.println();
                }
            }
            
            System.out.println("========================================");
            System.out.println("脚本执行完成");
            System.out.println("  总查询数: " + executedQueries);
            System.out.println("  成功: " + successQueries);
            System.out.println("  失败: " + failedQueries);
            System.out.println("========================================");
            
        } catch (IOException e) {
            System.err.println("错误: 无法读取文件 " + filePath);
            System.err.println(e.getMessage());
        } finally {
            cleanup();
        }
    }
    
    /**
     * 执行SQL语句
     */
    private void executeSQL(String sql) {
        executedQueries++;
        long startTime = System.currentTimeMillis();
        
        try {
            // 简化处理：直接识别命令
            String sqlUpper = sql.trim().toUpperCase();
            
            if (sqlUpper.startsWith("BEGIN")) {
                handleBegin();
            } else if (sqlUpper.startsWith("COMMIT")) {
                handleCommit();
            } else if (sqlUpper.startsWith("ROLLBACK")) {
                handleRollback();
            } else if (sqlUpper.startsWith("CREATE TABLE")) {
                handleCreateTableSimple(sql);
            } else if (sqlUpper.startsWith("INSERT")) {
                handleInsertSimple(sql);
            } else if (sqlUpper.startsWith("SELECT")) {
                handleSelectSimple(sql);
            } else if (sqlUpper.startsWith("UPDATE")) {
                handleUpdateSimple(sql);
            } else if (sqlUpper.startsWith("DELETE")) {
                handleDeleteSimple(sql);
            } else {
                System.err.println("✗ 不支持的命令");
                failedQueries++;
                return;
            }
            
            long duration = System.currentTimeMillis() - startTime;
            System.out.println("(执行时间: " + duration + "ms)");
            
            successQueries++;
            
        } catch (Exception e) {
            System.err.println("✗ 执行失败: " + e.getMessage());
            failedQueries++;
        }
    }
    
    /**
     * 简化处理CREATE TABLE
     */
    private void handleCreateTableSimple(String sql) throws Exception {
        // 提取表名
        String tableName = extractTableName(sql, "CREATE TABLE");
        CreateTableStatement stmt = new CreateTableStatement(tableName, java.util.Collections.emptyList());
        handleCreateTable(stmt);
    }
    
    /**
     * 简化处理INSERT
     */
    private void handleInsertSimple(String sql) throws Exception {
        String tableName = extractTableName(sql, "INSERT INTO");
        InsertStatement stmt = new InsertStatement(tableName, 
            java.util.Collections.emptyList(), 
            java.util.Collections.emptyList());
        handleInsert(stmt);
    }
    
    /**
     * 简化处理SELECT
     */
    private void handleSelectSimple(String sql) throws Exception {
        String tableName = extractTableName(sql, "FROM");
        SelectStatement stmt = new SelectStatement(null, tableName, null, null, null);
        handleSelect(stmt);
    }
    
    /**
     * 简化处理UPDATE
     */
    private void handleUpdateSimple(String sql) throws Exception {
        String tableName = extractTableName(sql, "UPDATE");
        UpdateStatement stmt = new UpdateStatement(tableName, 
            java.util.Collections.emptyList(), null);
        handleUpdate(stmt);
    }
    
    /**
     * 简化处理DELETE
     */
    private void handleDeleteSimple(String sql) throws Exception {
        String tableName = extractTableName(sql, "DELETE FROM");
        DeleteStatement stmt = new DeleteStatement(tableName, null);
        handleDelete(stmt);
    }
    
    /**
     * 提取表名
     */
    private String extractTableName(String sql, String keyword) {
        String upper = sql.toUpperCase();
        int startIdx = upper.indexOf(keyword) + keyword.length();
        String remaining = sql.substring(startIdx).trim();
        
        // 提取第一个单词作为表名
        int spaceIdx = remaining.indexOf(' ');
        int parenIdx = remaining.indexOf('(');
        
        int endIdx = -1;
        if (spaceIdx > 0 && parenIdx > 0) {
            endIdx = Math.min(spaceIdx, parenIdx);
        } else if (spaceIdx > 0) {
            endIdx = spaceIdx;
        } else if (parenIdx > 0) {
            endIdx = parenIdx;
        }
        
        if (endIdx > 0) {
            return remaining.substring(0, endIdx).trim();
        }
        return remaining.trim();
    }
    
    /**
     * 处理BEGIN事务
     */
    private void handleBegin() throws IOException {
        if (currentTxnId != null) {
            System.out.println("✗ 已经在事务中 (txn:" + currentTxnId + ")");
            return;
        }
        
        currentTxnId = txnManager.beginTransaction();
        autoCommit = false;
        System.out.println("✓ 事务开始 (txn:" + currentTxnId + ")");
    }
    
    /**
     * 处理COMMIT事务
     */
    private void handleCommit() {
        if (currentTxnId == null) {
            System.out.println("✗ 没有活跃的事务");
            return;
        }
        
        try {
            txnManager.commitTransaction(currentTxnId);
            System.out.println("✓ 事务已提交 (txn:" + currentTxnId + ")");
            currentTxnId = null;
            autoCommit = true;
        } catch (Exception e) {
            System.err.println("✗ 提交失败: " + e.getMessage());
        }
    }
    
    /**
     * 处理ROLLBACK事务
     */
    private void handleRollback() {
        if (currentTxnId == null) {
            System.out.println("✗ 没有活跃的事务");
            return;
        }
        
        try {
            txnManager.rollbackTransaction(currentTxnId);
            System.out.println("✓ 事务已回滚 (txn:" + currentTxnId + ")");
            currentTxnId = null;
            autoCommit = true;
        } catch (Exception e) {
            System.err.println("✗ 回滚失败: " + e.getMessage());
        }
    }
    
    /**
     * 处理CREATE TABLE
     */
    private void handleCreateTable(CreateTableStatement stmt) throws Exception {
        Executor.ExecuteResult result = executor.execute(stmt);
        
        if (result.isSuccess()) {
            System.out.println("✓ " + result.getMessage());
        } else {
            System.err.println("✗ " + result.getMessage());
        }
    }
    
    /**
     * 处理INSERT
     */
    private void handleInsert(InsertStatement stmt) throws Exception {
        // 如果不在事务中且自动提交，创建临时事务
        boolean tempTxn = false;
        if (currentTxnId == null && autoCommit) {
            currentTxnId = txnManager.beginTransaction();
            tempTxn = true;
        }
        
        if (currentTxnId != null) {
            // 使用TransactionManager执行
            String data = formatInsertData(stmt);
            txnManager.executeInsert(currentTxnId, stmt.getTableName(), data);
            System.out.println("✓ 插入成功 (1行)");
            
            // 临时事务自动提交
            if (tempTxn) {
                txnManager.commitTransaction(currentTxnId);
                currentTxnId = null;
            }
        } else {
            // 无事务模式，直接使用Executor
            Executor.ExecuteResult result = executor.execute(stmt);
            System.out.println("✓ " + result.getMessage());
        }
    }
    
    /**
     * 处理SELECT
     */
    private void handleSelect(SelectStatement stmt) throws Exception {
        // SELECT操作
        if (currentTxnId != null) {
            txnManager.executeSelect(currentTxnId, stmt.getTableName());
        }
        
        Executor.ExecuteResult result = executor.execute(stmt);
        
        if (result.isSuccess() && result.getResultSet() != null) {
            printResultSet(result.getResultSet());
        } else {
            System.out.println("✓ " + result.getMessage());
        }
    }
    
    /**
     * 处理UPDATE
     */
    private void handleUpdate(UpdateStatement stmt) throws Exception {
        boolean tempTxn = false;
        if (currentTxnId == null && autoCommit) {
            currentTxnId = txnManager.beginTransaction();
            tempTxn = true;
        }
        
        if (currentTxnId != null) {
            String data = formatUpdateData(stmt);
            txnManager.executeUpdate(currentTxnId, stmt.getTableName(), data);
            System.out.println("✓ 更新成功");
            
            if (tempTxn) {
                txnManager.commitTransaction(currentTxnId);
                currentTxnId = null;
            }
        } else {
            Executor.ExecuteResult result = executor.execute(stmt);
            System.out.println("✓ " + result.getMessage());
        }
    }
    
    /**
     * 处理DELETE
     */
    private void handleDelete(DeleteStatement stmt) throws Exception {
        boolean tempTxn = false;
        if (currentTxnId == null && autoCommit) {
            currentTxnId = txnManager.beginTransaction();
            tempTxn = true;
        }
        
        if (currentTxnId != null) {
            String data = formatDeleteData(stmt);
            txnManager.executeDelete(currentTxnId, stmt.getTableName(), data);
            System.out.println("✓ 删除成功");
            
            if (tempTxn) {
                txnManager.commitTransaction(currentTxnId);
                currentTxnId = null;
            }
        } else {
            Executor.ExecuteResult result = executor.execute(stmt);
            System.out.println("✓ " + result.getMessage());
        }
    }
    
    /**
     * 打印查询结果集
     */
    private void printResultSet(List<Map<String, Object>> resultSet) {
        if (resultSet.isEmpty()) {
            System.out.println("查询结果: 0行");
            return;
        }
        
        // 获取列名
        Map<String, Object> firstRow = resultSet.get(0);
        String[] columns = firstRow.keySet().toArray(new String[0]);
        
        // 打印表头
        System.out.println();
        for (String col : columns) {
            System.out.printf("%-15s", col);
        }
        System.out.println();
        System.out.println("=".repeat(columns.length * 15));
        
        // 打印数据行
        for (Map<String, Object> row : resultSet) {
            for (String col : columns) {
                Object value = row.get(col);
                System.out.printf("%-15s", value != null ? value.toString() : "NULL");
            }
            System.out.println();
        }
        
        System.out.println("\n查询结果: " + resultSet.size() + "行");
    }
    
    /**
     * 格式化INSERT数据
     */
    private String formatInsertData(InsertStatement stmt) {
        StringBuilder sb = new StringBuilder("{");
        for (int i = 0; i < stmt.getColumns().size(); i++) {
            if (i > 0) sb.append(", ");
            sb.append(stmt.getColumns().get(i)).append(":");
            sb.append(stmt.getValues().get(i));
        }
        sb.append("}");
        return sb.toString();
    }
    
    /**
     * 格式化UPDATE数据
     */
    private String formatUpdateData(UpdateStatement stmt) {
        StringBuilder sb = new StringBuilder("{");
        for (int i = 0; i < stmt.getAssignments().size(); i++) {
            if (i > 0) sb.append(", ");
            UpdateStatement.Assignment assign = stmt.getAssignments().get(i);
            sb.append(assign.getColumnName()).append(":").append(assign.getValue());
        }
        sb.append("}");
        return sb.toString();
    }
    
    /**
     * 格式化DELETE数据
     */
    private String formatDeleteData(DeleteStatement stmt) {
        if (stmt.getWhereClause() != null) {
            return stmt.getWhereClause().toString();
        }
        return "{}";
    }
    

    
    /**
     * 打印帮助信息
     */
    private void printHelp() {
        System.out.println("\n可用命令:");
        System.out.println("  BEGIN;              - 开始事务");
        System.out.println("  COMMIT;             - 提交事务");
        System.out.println("  ROLLBACK;           - 回滚事务");
        System.out.println("  CREATE TABLE ...;   - 创建表");
        System.out.println("  INSERT INTO ...;    - 插入数据");
        System.out.println("  SELECT ...;         - 查询数据");
        System.out.println("  UPDATE ...;         - 更新数据");
        System.out.println("  DELETE FROM ...;    - 删除数据");
        System.out.println("  status              - 显示状态");
        System.out.println("  help                - 显示帮助");
        System.out.println("  exit/quit           - 退出\n");
    }
    
    /**
     * 打印状态信息
     */
    private void printStatus() {
        System.out.println("\n当前状态:");
        System.out.println("  活跃事务: " + (currentTxnId != null ? "txn:" + currentTxnId : "无"));
        System.out.println("  自动提交: " + (autoCommit ? "开启" : "关闭"));
        System.out.println("  已执行查询: " + executedQueries);
        System.out.println("  成功: " + successQueries + ", 失败: " + failedQueries + "\n");
    }
    
    /**
     * 清理资源
     */
    private void cleanup() {
        try {
            txnManager.close();
        } catch (Exception e) {
            // 忽略
        }
    }
    
    /**
     * 主入口
     */
    public static void main(String[] args) {
        try {
            ISQL isql = new ISQL();
            
            if (args.length == 0) {
                // 交互式模式
                isql.interactiveMode();
            } else if (args.length == 2 && args[0].equals("-f")) {
                // 脚本模式
                isql.scriptMode(args[1]);
            } else {
                System.err.println("用法:");
                System.err.println("  交互式模式: java -jar isql.jar");
                System.err.println("  脚本模式:   java -jar isql.jar -f script.sql");
                System.exit(1);
            }
            
        } catch (Exception e) {
            System.err.println("错误: " + e.getMessage());
            e.printStackTrace();
            System.exit(1);
        }
    }
}
